<?php

class Grillas_adv_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    private function Strip($value) {
        if (get_magic_quotes_gpc() != 0) {
            if (is_array($value))
                if ($this->array_is_associative($value)) {
                    foreach ($value as $k => $v)
                        $tmp_val[$k] = stripslashes($v);
                    $value = $tmp_val;
                }
                else
                    for ($j = 0; $j < sizeof($value); $j++)
                        $value[$j] = stripslashes($value[$j]);
            else
                $value = stripslashes($value);
        }
        return $value;
    }

    private function getStringForGroup($group) {
        $i_ = '';
        $sopt = array('eq' => "=", 'ne' => "<>", 'lt' => "<", 'le' => "<=", 'gt' => ">", 'ge' => ">=", 'bw' => " {$i_}LIKE ", 'bn' => " NOT {$i_}LIKE ", 'in' => ' IN ', 'ni' => ' NOT IN', 'ew' => " {$i_}LIKE ", 'en' => " NOT {$i_}LIKE ", 'cn' => " {$i_}LIKE ", 'nc' => " NOT {$i_}LIKE ", 'nu' => 'IS NULL', 'nn' => 'IS NOT NULL');
        $s = "(";
        if (isset($group['groups']) && is_array($group['groups']) && count($group['groups']) > 0) {
            for ($j = 0; $j < count($group['groups']); $j++) {
                if (strlen($s) > 1) {
                    $s .= " " . $group['groupOp'] . " ";
                }
                try {
                    $dat = $this->getStringForGroup($group['groups'][$j]);
                    $s .= $dat;
                } catch (Exception $e) {
                    echo $e->getMessage();
                }
            }
        }
        if (isset($group['rules']) && count($group['rules']) > 0) {
            try {
                foreach ($group['rules'] as $key => $val) {
                    if (strlen($s) > 1) {
                        $s .= " " . $group['groupOp'] . " ";
                    }
                    $field = $val['field'];
                    $op = $val['op'];
                    $v = $val['data'];
                    if ($op) {
                        switch ($op) {
                            case 'bw':
                            case 'bn':
                                $s .= $field . ' ' . $sopt[$op] . "'$v%'";
                                break;
                            case 'ew':
                            case 'en':
                                $s .= $field . ' ' . $sopt[$op] . "'%$v'";
                                break;
                            case 'cn':
                            case 'nc':
                                $s .= $field . ' ' . $sopt[$op] . "'%$v%'";
                                break;
                            case 'in':
                            case 'ni':
                                $s .= $field . ' ' . $sopt[$op] . "( '$v' )";
                                break;
                            case 'nu':
                            case 'nn':
                                $s .= $field . ' ' . $sopt[$op] . " ";
                                break;
                            default :
                                $s .= $field . ' ' . $sopt[$op] . " '$v' ";
                                break;
                        }
                    }
                }
            } catch (Exception $e) {
                echo $e->getMessage();
            }
        }
        $s .= ")";
        if ($s == "()") {
            //return array("",$prm); // ignore groups that don't have rules
            return " 1=1 ";
        } else {
            return $s;
            ;
        }
    }

//fin private function getStringForGroup($group){

    private function array_is_associative($array) {
        if (is_array($array) && !empty($array)) {
            for ($iterator = count($array) - 1; $iterator; $iterator--) {
                if (!array_key_exists($iterator, $array)) {
                    return true;
                }
            }
            return!array_key_exists(0, $array);
        }
        return false;
    }

    public function ofertasLaborales($opc) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sqlAux = "";
        $whereAux = "";
        switch ($opc) {
            case 'I':
                $instituId = $this->session->userdata("tablaUsuarioId");
                $sqlAux = " inner join instituciones insti on insti.ciudadId = ofer.ciudadId ";
                $whereAux = " and insti.institucionId = '$instituId'";
                break;
        }
        $sql = "select count(ofer.ofertaLaboralId) as total from ofertaslaborales ofer 
            left join tipdiscapacidades tipDis on tipDis.tipDiscapacidadid = ofer.tipDiscapacidadid
            inner join empresas emps on emps.empresaId = ofer.empresaId 
            left join sectores sect on sect.`sectorId` = emps.`sectorId`
            inner join ciudades ciu on ciu.ciudadId = ofer.ciudadId 
            inner join jornadas jor on jor.jornadaId = ofer.jornadaId
            left join competencias compe on compe.competenciaId = ofer.competenciaId
            $sqlAux
            inner join departamentos dept on dept.dptCod = ciu.dptCod where ofer.ofertaLaboralEstado ='A' and ofer.ofertaLaboralFechaCierre > now() $whereAux " . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select * from ofertaslaborales ofer 
            left join tipdiscapacidades tipDis on tipDis.tipDiscapacidadid = ofer.tipDiscapacidadid
            inner join empresas emps on emps.empresaId = ofer.empresaId 
            left join sectores sect on sect.`sectorId` = emps.`sectorId`
            inner join ciudades ciu on ciu.ciudadId = ofer.ciudadId 
            inner join jornadas jor on jor.jornadaId = ofer.jornadaId
            left join competencias compe on compe.competenciaId = ofer.competenciaId
            $sqlAux
            inner join departamentos dept on dept.dptCod = ciu.dptCod where ofer.ofertaLaboralEstado ='A' and ofer.ofertaLaboralFechaCierre > now() $whereAux " . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
//        exit($sql);
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        $rol = $this->session->userdata("rolId");
        foreach ($query->result() as $value) {
            if ($rol == "3") //es una empresa
                $sql = "select * from postulaciones where ofertaLaboralId = '{$value->ofertaLaboralId}' and institucionId = '{$this->session->userdata("tablaUsuarioId")}' and postulacionEstado = 'A'";
            elseif ($rol == "4") //es una persona
                $sql = "select * from postulaciones where ofertaLaboralId = '{$value->ofertaLaboralId}' and personasId = '{$this->session->userdata("tablaUsuarioId")}' and postulacionEstado = 'A'";
            $tmp = ($this->db->query($sql)->num_rows() > 0) ? "Ver" : "No hay";
            $responce->rows[$i]['id'] = $value->ofertaLaboralId; //echo "<row id='".$value->tempId."'>";
            $tipDiscapacidad = (isset($value->tipDiscapacidad))?$value->tipDiscapacidad:"No prioriza";
            if ($rol == "3") //es una empresa
                $responce->rows[$i]['cell'] = array('', $tmp, $value->ofertaLaboralCargo,$tipDiscapacidad, $value->jornadaNombre, $value->ofertaLaboralFechaCierre, $value->ciudad, $value->departamento, $value->empresaRazon, $value->sector);
            elseif ($rol == "4")
                $responce->rows[$i]['cell'] = array($tmp, $value->ofertaLaboralCargo,$tipDiscapacidad, $value->jornadaNombre, $value->ofertaLaboralFechaCierre, $value->ciudad, $value->departamento, $value->sector);
            $i++;
        }
        echo json_encode($responce);
    }

    public function consulPer($transaccion) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(transaccion) as total from temporal WHERE transaccion = '$transaccion'" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select tempId,tempInt1,tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempVarchar5  from temporal where transaccion = '$transaccion' " . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->tempInt1; //echo "<row id='".$value->tempId."'>";
            $responce->rows[$i]['cell'] = array('', $value->tempVarchar5, $value->tempVarchar1,$value->tempVarchar2, $value->tempVarchar3, $value->tempVarchar4);
            $i++;
        }
        echo json_encode($responce);
    }

    public function consultaPostulados($oferta) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(postulacionesId) as total from postulaciones where postulacionEstado = 'A' and institucionId = '{$this->session->userdata("tablaUsuarioId")}' and ofertaLaboralId = '$oferta'" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select postu.postulacionesId,postu.postulacionPreseleccion,postu.postulacionFechaPreseleccion,perso.personaTipoDocumento,perso.personaDocumento,perso.personaNombres,perso.personaApellidos,postulacionFecha from postulaciones postu inner join personas perso on perso.personasId = postu.personasId where postu.postulacionEstado = 'A' and postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}' and postu.ofertaLaboralId = '$oferta' " . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->postulacionesId;
            $responce->rows[$i]['cell'] = array('', $value->postulacionPreseleccion, $value->postulacionFechaPreseleccion, $value->personaTipoDocumento, $value->personaDocumento, $value->personaNombres, $value->personaApellidos, $value->postulacionFecha);
            $i++;
        }
        echo json_encode($responce);
    }

    public function conPostu($transa) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(transaccion) as total from temporal where transaccion = '$transa'" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select tempInt1,tempVarchar1,tempVarchar2,tempVarchar3,tempDate1 from temporal  where transaccion = '$transa'" . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->tempInt1;
            $responce->rows[$i]['cell'] = array('', $value->tempVarchar3, $value->tempDate1, $value->tempVarchar1, $value->tempVarchar2);
            $i++;
        }
        echo json_encode($responce);
    }

    public function conSolicitudes($transa) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(transaccion) as total from temporal where transaccion = '$transa'" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select tempInt1,tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempVarchar5,tempInt2,tempDate1 from temporal  where transaccion = '$transa'" . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->tempInt1;
            $responce->rows[$i]['cell'] = array($value->tempInt2, $value->tempVarchar5, $value->tempDate1, substr($value->tempVarchar1,0,15), $value->tempVarchar2, $value->tempVarchar3, $value->tempVarchar4);
            $i++;
        }
        echo json_encode($responce);
    }

    public function consInstitucione() {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(institucionId) as total from instituciones where 1 = 1" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select insti.institucionId,insti.institucionNit,insti.institucionRazon,insti.institucionDireccion,insti.institucionTelefono,insti.institucionPagina,insti.institucionContacto,insti.institucionTipoFormacion,insti.institucionEnfoquesFormacion,usr.usuario from instituciones insti inner join usuarios usr on usr.usuarioId = insti.usuarioId  where 1 = 1" . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->institucionId;
            $responce->rows[$i]['cell'] = array('', $value->institucionNit, $value->institucionRazon, $value->institucionDireccion, $value->institucionTelefono, $value->institucionPagina, $value->institucionContacto, $value->usuario, $value->institucionTipoFormacion, $value->institucionEnfoquesFormacion);
            $i++;
        }
        echo json_encode($responce);
    }

    public function servyasesoria() {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(solServyAseId) as total from solservyase where empresaId != '' and solServyAseEstado = 'A'" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select sol.solServyAseId,sol.`solServyAseServicios`,sol.solServyAseDescripcion,sol.solServyAseNombre,sol.solServyAseSalBase,sol.solServyAseSalTope,emps.empresaRazon,emps.empresaTelefono1,dept.departamento,ciu.ciudad from solservyase sol inner join empresas emps on emps.empresaId = sol.empresaId inner join ciudades ciu on ciu.ciudadId = sol.ciudadId inner join departamentos dept on dept.dptCod = ciu.dptCod WHERE sol.solServyAseEstado = 'A' " . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->solServyAseId;
            $servicios = unserialize($value->solServyAseServicios);
            $serviciosText = "";
            if (is_array($servicios)) {
                foreach ($servicios as $serv) {
                    $sql = "select `servInclusionNombre` from serviciosinclusion where `servInclusionId` = '$serv' and `servInclusionEstado` = 'A'";
                    $tmp = $this->db->query($sql)->result();
                    $serviciosText .= "," . $tmp[0]->servInclusionNombre;
                }
                $serviciosText = trim($serviciosText, ",");
            }
            $responce->rows[$i]['cell'] = array('', substr($value->empresaRazon, 0, 11), $value->solServyAseNombre, $value->solServyAseDescripcion, $value->departamento, $value->ciudad, $serviciosText);
            $i++;
        }
        echo json_encode($responce);
    }

    public function sigueOferEmp() {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(contra.contratoId) as total from contratos contra
            inner join postulaciones postu on postu.postulacionesId = contra.postulacionesId
            inner join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId
            inner join empresas emps on emps.empresaId = ofer.empresaId
            where contra.contratoEstado = 'A' and postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}'" . $wh . " group by emps.empresaId";
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select emps.empresaId,emps.empresaRazon,emps.empresaNit,emps.empresaContacto,emps.empresaTelefono1 from contratos contra
            inner join postulaciones postu on postu.postulacionesId = contra.postulacionesId
            inner join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId
            inner join empresas emps on emps.empresaId = ofer.empresaId
            where contra.contratoEstado = 'A' and postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}'" . $wh . " group by emps.empresaId  ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->empresaId;
            $responce->rows[$i]['cell'] = array($value->empresaRazon, $value->empresaNit, $value->empresaContacto, $value->empresaTelefono1);
            $i++;
        }
        echo json_encode($responce);
    }

    public function sigueOferEmpDet($empresa) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select contra.contratoId from personas per
                inner join postulaciones postu on postu.personasId = per.personasId
                inner join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId
                inner join empresas emps on emps.empresaId = ofer.empresaId
                inner join contratos contra on contra.postulacionesId = postu.postulacionesId
                where contra.contratoEstado = 'A' and postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}' and emps.empresaId = '$empresa'" . $wh;
        $count = $this->db->query($sql)->num_rows();
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select contra.contratoId,per.personaNombres,per.personaApellidos,per.personaTipoDocumento,per.personaDocumento,contra.contratoFecha,ofer.ofertaLaboralCargo from personas per
                inner join postulaciones postu on postu.personasId = per.personasId
                inner join ofertaslaborales ofer on ofer.ofertaLaboralId = postu.ofertaLaboralId
                inner join empresas emps on emps.empresaId = ofer.empresaId
                inner join contratos contra on contra.postulacionesId = postu.postulacionesId
                where contra.contratoEstado = 'A' and postu.institucionId = '{$this->session->userdata("tablaUsuarioId")}' and emps.empresaId = '$empresa'" . $wh . " ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->contratoId;
            $responce->rows[$i]['cell'] = array('', $value->personaTipoDocumento, $value->personaDocumento, $value->personaNombres, $value->personaApellidos, $value->ofertaLaboralCargo, $value->contratoFecha);
            $i++;
        }
        echo json_encode($responce);
    }

    public function segPersona($contra) {
        //Variables del jqgrid
        $page = $this->input->post('page'); // get the requested page
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        if (!$sidx)
            $sidx = 1;
        $wh = "";
        $searchOn = $this->Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = $this->Strip($_REQUEST['filters']);
            $jsona = json_decode($searchstr, true);
            $wh = " AND " . $this->getStringForGroup($jsona);
        }
        $sql = "select count(seguimientoContatosId) as total from seguimientocontatos where contratoId = '$contra'" . $wh;
        $query = $this->db->query($sql);
        $obj = $query->result();
        $count = $obj[0]->total;
        if ($count > 0)
            $total_pages = ceil($count / $limit);
        else
            $total_pages = 0;
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        $start = ($start > 0) ? $start : 0;
        $sql = "select seguimientoContatosId,seguimientoContatoActividadRealizada,seguimientoContatoFechaCreo,seguimientoContatoDebilidades,seguimientoContatoFortalezas,seguimientoContatoMejoras,seguimientoContatoLogros from seguimientocontatos where contratoId = '$contra'" . $wh . "  ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $query = $this->db->query($sql);
        $responce->page = $page;
        $responce->total = $total_pages;
        $responce->records = $count;
        $i = 0;
        foreach ($query->result() as $value) {
            $responce->rows[$i]['id'] = $value->seguimientoContatosId;
            $responce->rows[$i]['cell'] = array($value->seguimientoContatoFechaCreo, $value->seguimientoContatoActividadRealizada, $value->seguimientoContatoDebilidades, $value->seguimientoContatoFortalezas, $value->seguimientoContatoMejoras, $value->seguimientoContatoLogros);
            $i++;
        }
        echo json_encode($responce);
    }

}

?>
